﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Net;
using System.Net.Sockets;
using System.Runtime.InteropServices;
using System.Text;
using System.Windows.Forms;

namespace LSQL
{
    public class SQL
    {
        public static SqlConnection con;



        public static int InsertDataAndGetId(string connectionString, string tableName, string[] columnNames, object[] columnValues)
        {
            if (columnNames.Length != columnValues.Length)
            {
                throw new ArgumentException("Column names and values must have the same length.");
            }

            string columnNamesStr = string.Join(", ", columnNames.Select(c => $"[{c}]"));
            string paramNamesStr = string.Join(", ", columnNames.Select(c => $"@{c}"));

            string insertSql = $"INSERT INTO [{tableName}] ({columnNamesStr}) VALUES ({paramNamesStr}); SELECT SCOPE_IDENTITY();";

            using (SqlConnection connection = new SqlConnection(connectionString))
            using (SqlCommand command = new SqlCommand(insertSql, connection))
            {
                connection.Open();
                for (int i = 0; i < columnNames.Length; i++)
                {
                    command.Parameters.AddWithValue($"@{columnNames[i]}", columnValues[i]);
                }

                return Convert.ToInt32(command.ExecuteScalar());
            }
        }

        public static bool CheckServerConnection(string IP, int port)
        {
            try
            {
                using (TcpClient client = new TcpClient(IP, port))
                {
                    return true;
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("连接失败：" + ex.Message);
                return false;
            }
        }

        /// <summary>
        /// 倒叙查询符合查询条件的内容
        /// </summary>
        /// <param name="Address">数据库地址</param>
        /// <param name="Table">表名</param>
        /// <param name="Condition">查询条件</param>
        /// <param name="Content">内容</param>
        public static string InvertedSelect(string Address, string Table, string Condition, string Content)//查询
        {
            try
            {
                string connetStr = Address;
                con = new SqlConnection(connetStr);
                con.Open();
                string sqlCmd = "select * from " + Table + " where " + Condition + " order by ID desc";
                SqlCommand cmd = new SqlCommand(sqlCmd, con);
                SqlDataReader reader = cmd.ExecuteReader();
                reader.Read();
                string a = reader[Content].ToString();
                con.Close();
                return a;
            }
            catch (SqlException ex)
            {
                con.Close();
                Console.WriteLine(ex.Message);
                return "false";
            }
        }

        /// <summary>
        /// 增加一行并返回ID
        /// </summary>
        /// <param name="Address">数据库地址</param>
        /// <param name="Table">表名</param>
        /// <param name="Column">列名</param>
        /// <param name="Content">内容</param>
        public static int Insert(string Address, string Table, string Column, string Content)//新增
        {
            try
            {
                string connetStr = Address;
                con = new SqlConnection(connetStr);
                con.Open();
                string sqlstr = "INSERT INTO " + Table + " (" + Column + ") VALUES (" + Content + ");";//调用使用所有内容项都需要加单引号(')
                SqlCommand cmd = new SqlCommand(sqlstr, con);
                int res = cmd.ExecuteNonQuery();
                con.Close();
                return res;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
                con.Close();
                return 0;
            }
        }

        /// <summary>
        /// 增加一行并返回ID
        /// </summary>
        /// <param name="Address">数据库地址</param>
        /// <param name="Table">表名</param>
        /// <param name="Column">列名</param>
        /// <param name="Content">内容</param>
        public static int InsertReturnID(string Address, string Table, string Column, string Content)//新增
        {
            try
            {
                string connetStr = Address;
                con = new SqlConnection(connetStr);
                con.Open();
                string sqlstr = "INSERT INTO " + Table + " (" + Column + ") VALUES (" + Content + ");SELECT SCOPE_IDENTITY();";//调用使用所有内容项都需要加单引号(')
                SqlCommand cmd = new SqlCommand(sqlstr, con);
                object result = cmd.ExecuteScalar(); // 获取插入的ID
                con.Close();
                int res = 0;
                return res = Convert.ToInt32(result);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
                con.Close();
                return 0;
            }
        }

        /// <summary>
        /// 增加对行并返回添加行数，sql语句需全写
        /// </summary>
        /// <param name="Address">数据库地址</param>
        /// <param name="sql">sql语句</param>
        public static int Insert2(string Address, string sql)//新增
        {
            try
            {
                string connetStr = Address;
                con = new SqlConnection(connetStr);
                con.Open();
                string sqlstr = sql;
                SqlCommand cmd = new SqlCommand(sqlstr, con);
                int res = cmd.ExecuteNonQuery();
                con.Close();
                return res;
            }
            catch (Exception)
            {
                con.Close();
                return 0;
            }
        }

        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="Address">数据库地址</param>
        /// <param name="Table">表名</param>
        /// <param name="Condition">条件</param>
        /// <param name="Content">内容</param>
        public static bool Update(string Address, string Table, string Content, string Condition)//单条件修改
        {
            try
            {
                string connetStr = Address;
                con = new SqlConnection(connetStr);
                con.Open();
                string sqlCmd = "UPDATE " + Table + " SET " + Content + " WHERE " + Condition + ";";
                SqlCommand cmd = new SqlCommand(sqlCmd, con);
                int res = cmd.ExecuteNonQuery();
                con.Close();
                return true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                con.Close();
                return false;
            }
        }

        /// <summary>
        /// 删除一行并返回bool
        /// </summary>
        /// <param name="Address">数据库地址</param>
        /// <param name="Table">表名</param>
        /// <param name="Condition">条件</param>
        public static bool Delete(string Address, string Table, string Condition)
        {
            try
            {
                string connetStr = Address;
                con = new SqlConnection(connetStr);
                con.Open();
                string sqlCmd = " DELETE FROM " + Table + " WHERE (" + Condition + ");";
                SqlCommand cmd = new SqlCommand(sqlCmd, con);
                int res = cmd.ExecuteNonQuery();
                con.Close();
                if (res > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception)
            {
                con.Close();
                return false;
            }
        }

        /// <summary>
        /// 无条件的返回表中行数
        /// </summary>
        /// <param name="Address">数据库地址</param>
        /// <param name="Table">表名</param>
        public static int ReturnRowsCount1(string Address, string Table)
        {
            DataTable dt = new DataTable();
            try
            {
                con = new SqlConnection(Address);
                string sqlCmd = "select * from " + Table + " order by ID desc";
                SqlCommand cmd = new SqlCommand(sqlCmd, con);
                SqlDataAdapter sda = new SqlDataAdapter();
                sda.SelectCommand = cmd;
                DataSet ds = new DataSet();
                sda.Fill(ds, Table);
                dt = ds.Tables[0];
                int count = dt.Rows.Count;
                return count;
            }
            catch (Exception)
            {
                return 0;
            }
        }

        /// <summary>
        /// 有条件的返回表中行数,条件无需加单引号
        /// </summary>
        /// <param name="Address">数据库地址</param>
        /// <param name="Table">表名</param>
        /// <param name="Condition">条件</param>
        public static int ReturnRowsCount2(string Address, string Table, string Condition)
        {
            DataTable dt = new DataTable();
            try
            {
                con = new SqlConnection(Address);
                string sqlCmd = "select * from " + Table + " where " + Condition + " order by ID desc";
                SqlCommand cmd = new SqlCommand(sqlCmd, con);
                SqlDataAdapter sda = new SqlDataAdapter();
                sda.SelectCommand = cmd;
                DataSet ds = new DataSet();
                sda.Fill(ds, Table);
                dt = ds.Tables[0];
                int count = dt.Rows.Count;
                return count;
            }
            catch (Exception)
            {
                return 0;
            }
        }

        /// <summary>
        /// 有条件的返回表中行数,条件无需加单引号,不排序
        /// </summary>
        /// <param name="Address">数据库地址</param>
        /// <param name="Table">表名</param>
        /// <param name="Condition">条件</param>
        public static int ReturnRowsCount3(string Address, string Table, string Condition)
        {
            DataTable dt = new DataTable();
            try
            {
                con = new SqlConnection(Address);
                string sqlCmd = "select * from " + Table + " where " + Condition;
                SqlCommand cmd = new SqlCommand(sqlCmd, con);
                SqlDataAdapter sda = new SqlDataAdapter();
                sda.SelectCommand = cmd;
                DataSet ds = new DataSet();
                sda.Fill(ds, Table);
                dt = ds.Tables[0];
                int count = dt.Rows.Count;
                return count;
            }
            catch (Exception)
            {
                return 0;
            }
        }

        /// <summary>
        /// 有条件返回给定列名下的所有行内容，格式为string[]
        /// </summary>
        /// <param name="Address">数据库地址</param>
        /// <param name="Table">表名</param>
        /// <param name="Condition">条件</param>
        /// <param name="ColumnName">给定的列名</param>
        public static string[] ArrayList(string Address, string Table, string Condition, string ColumnName)
        {
            string connetStr = Address;
            SqlConnection con = new SqlConnection(connetStr);
            con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "select * from " + Table + " where " + Condition + "";
            cmd.CommandType = CommandType.Text;
            SqlDataReader sdr = cmd.ExecuteReader();
            ArrayList arrayList = new ArrayList();
            while (sdr.Read())
            {
                arrayList.Add(sdr[ColumnName].ToString());
            }
            con.Close();
            return (string[])arrayList.ToArray(typeof(string));
        }

        /// <summary>
        /// 有条件返回给定列名下的行内容，不包含重复项，格式为string[]
        /// </summary>
        /// <param name="Address">数据库地址</param>
        /// <param name="Table">表名</param>
        /// <param name="Condition">条件</param>
        /// <param name="ColumnName">给定的列名</param>
        public static string[] ArrayList1(string Address, string Table, string Condition, string ColumnName)
        {
            string connetStr = Address;
            SqlConnection con = new SqlConnection(connetStr);
            con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "select * from " + Table + " where " + Condition + "";
            cmd.CommandType = CommandType.Text;
            SqlDataReader sdr = cmd.ExecuteReader();
            ArrayList arrayList = new ArrayList();
            while (sdr.Read())
            {
                if (!arrayList.Contains(sdr[ColumnName].ToString()))
                {
                    arrayList.Add(sdr[ColumnName].ToString());
                }
            }
            con.Close();
            return (string[])arrayList.ToArray(typeof(string));
        }

        /// <summary>
        /// 返回dgv
        /// </summary>
        /// <param name="Address">数据库地址</param>
        /// <param name="Table">表名</param>
        /// <param name="dgv">dgv</param>
        public static void ReturnDGV(string Address, string Table, DataGridView dgv)
        {
            con = new SqlConnection(Address);
            string sqlCmd = "select * from " + Table + " order by ID desc";
            SqlCommand cmd = new SqlCommand(sqlCmd, con);
            SqlDataAdapter sda = new SqlDataAdapter();
            sda.SelectCommand = cmd;
            DataSet ds = new DataSet();
            sda.Fill(ds, Table);
            dgv.DataSource = ds.Tables[0];
        }

        /// <summary>
        /// 有条件的返回dgv
        /// </summary>
        /// <param name="Address">数据库地址</param>
        /// <param name="Table">表名</param>
        /// <param name="Condition">条件</param>
        /// <param name="dgv">dgv</param>
        public static void ReturnDGV2(string Address, string Table, string Condition, DataGridView dgv)
        {
            con = new SqlConnection(Address);
            string sqlCmd = "select * from " + Table + " where " + Condition + " order by ID desc";
            SqlCommand cmd = new SqlCommand(sqlCmd, con);
            SqlDataAdapter sda = new SqlDataAdapter();
            sda.SelectCommand = cmd;
            DataSet ds = new DataSet();
            sda.Fill(ds, Table);
            dgv.DataSource = ds.Tables[0];
        }

        /// <summary>
        /// 无条件返回dgv，最近200条数量
        /// </summary>
        /// <param name="Address">数据库地址</param>
        /// <param name="Table">表名</param>
        /// <param name="dgv">dgv</param>
        public static void ReturnDGV3(string Address, string Table, DataGridView dgv)
        {
            con = new SqlConnection(Address);
            string sqlCmd = "select top 200 * from " + Table + " order by ID desc";
            SqlCommand cmd = new SqlCommand(sqlCmd, con);
            SqlDataAdapter sda = new SqlDataAdapter();
            sda.SelectCommand = cmd;
            DataSet ds = new DataSet();
            sda.Fill(ds, Table);
            dgv.DataSource = ds.Tables[0];
        }

        /// <summary>
        /// 无条件正序返回dgv
        /// </summary>
        /// <param name="Address">数据库地址</param>
        /// <param name="Table">表名</param>
        /// <param name="dgv">dgv</param>
        public static void ReturnDGV4(string Address, string Table, DataGridView dgv)
        {
            con = new SqlConnection(Address);
            string sqlCmd = "select * from " + Table;
            SqlCommand cmd = new SqlCommand(sqlCmd, con);
            SqlDataAdapter sda = new SqlDataAdapter();
            sda.SelectCommand = cmd;
            DataSet ds = new DataSet();
            sda.Fill(ds, Table);
            dgv.DataSource = ds.Tables[0];
        }

        /// <summary>
        /// 查询数据库并返回符合条件的所有行作为 DataRow[]。
        /// </summary>
        /// <param name="connectionString">数据库连接字符串。</param>
        /// <param name="query">SQL 查询语句。</param>
        /// <returns>返回查询结果的所有行 DataRow[]，如果没有结果则返回空数组。</returns>
        public static DataRow[] ReturnDataRows(string connectionString, string query)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    // 打开数据库连接
                    connection.Open();

                    using (SqlCommand command = new SqlCommand(query, connection))
                    {
                        // 执行查询并加载结果到 DataTable
                        using (SqlDataAdapter adapter = new SqlDataAdapter(command))
                        {
                            DataTable table = new DataTable();
                            adapter.Fill(table);

                            // 返回 DataRow[]，如果没有数据返回空数组
                            return table.Select();
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("数据库查询出错：" + ex.Message);
                return Array.Empty<DataRow>();
            }
        }

        public static DataTable ReturnDataTableTop(string connectionString, int departmentId,int level)
        {
            DataTable dt = new DataTable();
            try
            {
                using (SqlConnection con = new SqlConnection(connectionString))
                {
                    string sqlCmd = @"
                ;WITH DepartmentHierarchy AS (
                    SELECT 
                        ID,
                        DepartmentName,
                        ParentDepartmentID,
                        departmentLevel,
                        0 as HierarchyLevel,
                        CAST(DepartmentName as nvarchar(1000)) as Path
                    FROM Departments
                    WHERE ID =  @DepartmentID AND Del_tag = 0
                    
                    UNION ALL
                    
                    SELECT 
                        d.ID,
                        d.DepartmentName,
                        d.ParentDepartmentID,
                        d.departmentLevel,
                        dh.HierarchyLevel + 1,
                        CAST(dh.Path + ' > ' + d.DepartmentName as nvarchar(1000))
                    FROM Departments d
                    INNER JOIN DepartmentHierarchy dh ON d.ParentDepartmentID = dh.ID
                    WHERE d.Del_tag = 0
                )
                SELECT 
                    ID,
                    DepartmentName,
                    ParentDepartmentID,
                    departmentLevel,
                    HierarchyLevel,
                    Path as DepartmentPath
                FROM DepartmentHierarchy
                WHERE departmentLevel = @level
                ORDER BY HierarchyLevel, ID
                OPTION (MAXRECURSION 0);
            ";

                    using (SqlCommand cmd = new SqlCommand(sqlCmd, con))
                    {
                        // 用参数代替 DECLARE
                        cmd.Parameters.AddWithValue("@DepartmentID", departmentId);
                        cmd.Parameters.AddWithValue("@level", level);

                        using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
                        {
                            sda.Fill(dt); // ✅ 正确写法，不传 query 当表名
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message); // 打印错误便于排查
            }
            return dt;
        }

        /// <summary>
        /// 无条件查询SQL并返回dataTable，查询语句全写
        /// </summary>
        /// <param name="Address">数据库地址</param>
        /// <param name="query">查询语句</param>
        public static DataTable ReturnDataTable(string Address, string query)
        {
            DataTable dt = new DataTable();
            try
            {
                con = new SqlConnection(Address);
                string sqlCmd = query;
                //string sqlCmd = "select * from " + Table + " order by ID desc";
                SqlCommand cmd = new SqlCommand(sqlCmd, con);
                SqlDataAdapter sda = new SqlDataAdapter();
                sda.SelectCommand = cmd;
                DataSet ds = new DataSet();
                sda.Fill(ds, query);
                dt = ds.Tables[0];
                return dt;
            }
            catch (Exception)
            {
                return dt;
            }
        }


        /// <summary>
        /// 无条件倒序查询MySQL并返回dataTable
        /// </summary>
        /// <param name="Address">数据库地址</param>
        /// <param name="Table">表名</param>
        public static DataTable ReturnDataTable1(string Address, string Table)
        {
            DataTable dt = new DataTable();
            try
            {
                con = new SqlConnection(Address);
                string sqlCmd = "select * from " + Table;
                //string sqlCmd = "select * from " + Table + " order by ID desc";
                SqlCommand cmd = new SqlCommand(sqlCmd, con);
                SqlDataAdapter sda = new SqlDataAdapter();
                sda.SelectCommand = cmd;
                DataSet ds = new DataSet();
                sda.Fill(ds, Table);
                dt = ds.Tables[0];
                return dt;
            }
            catch (Exception)
            {
                return dt;
            }
        }

        /// <summary>
        /// 有条件倒序查询MySQL并返回dataTable
        /// </summary>
        /// <param name="Address">数据库地址</param>
        /// <param name="Table">表名</param>
        /// <param name="Condition">条件</param>
        public static DataTable ReturnDataTableAsc(string Address, string Table, string Condition)
        {
            DataTable dt = new DataTable();
            try
            {
                con = new SqlConnection(Address);
                string sqlCmd = "select * from " + Table + " where " + Condition + " order by ID";
                SqlCommand cmd = new SqlCommand(sqlCmd, con);
                SqlDataAdapter sda = new SqlDataAdapter();
                sda.SelectCommand = cmd;
                DataSet ds = new DataSet();
                sda.Fill(ds, Table);
                dt = ds.Tables[0];
                return dt;
            }
            catch (Exception)
            {
                return dt;
            }
        }

        /// <summary>
        /// 有条件倒序查询MySQL并返回dataTable
        /// </summary>
        /// <param name="Address">数据库地址</param>
        /// <param name="Table">表名</param>
        /// <param name="Condition">条件</param>
        public static DataTable ReturnDataTable2(string Address, string Table, string Condition)
        {
            DataTable dt = new DataTable();
            try
            {
                con = new SqlConnection(Address);
                string sqlCmd = "select * from " + Table + " where " + Condition + " order by ID desc";
                SqlCommand cmd = new SqlCommand(sqlCmd, con);
                SqlDataAdapter sda = new SqlDataAdapter();
                sda.SelectCommand = cmd;
                DataSet ds = new DataSet();
                sda.Fill(ds, Table);
                dt = ds.Tables[0];
                return dt;
            }
            catch (Exception)
            {
                return dt;
            }
        }

        /// <summary>
        /// 有条件倒序查询MySQL特定列去重并返回dataTable
        /// </summary>
        /// <param name="Address">数据库地址</param>
        /// <param name="Table">表名</param>
        /// <param name="Condition">条件</param>
        public static DataTable ReturnDataTableDist(string Address, string Column, string Table, string Condition)
        {
            DataTable dt = new DataTable();
            try
            {
                con = new SqlConnection(Address);
                string sqlCmd = "select distinct " + Column + "  from " + Table + " where " + Condition;
                SqlCommand cmd = new SqlCommand(sqlCmd, con);
                SqlDataAdapter sda = new SqlDataAdapter();
                sda.SelectCommand = cmd;
                DataSet ds = new DataSet();
                sda.Fill(ds, Table);
                dt = ds.Tables[0];
                return dt;
            }
            catch (Exception)
            {
                return dt;
            }
        }

        /// <summary>
        /// 有条件无序查询MySQL并返回dataTable
        /// </summary>
        /// <param name="Address">数据库地址</param>
        /// <param name="Table">表名</param>
        /// <param name="Condition">条件</param>
        public static DataTable ReturnDataTable3(string Address, string Table, string Condition)
        {
            DataTable dt = new DataTable();
            try
            {
                con = new SqlConnection(Address);
                string sqlCmd = "select distinct * from " + Table + " where " + Condition;
                SqlCommand cmd = new SqlCommand(sqlCmd, con);
                SqlDataAdapter sda = new SqlDataAdapter();
                sda.SelectCommand = cmd;
                DataSet ds = new DataSet();
                sda.Fill(ds, Table);
                dt = ds.Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return dt;
            }
        }

        /// <summary>
        /// 有条件无序去重查询某列并返回dataTable
        /// </summary>
        /// <param name="Address">数据库地址</param>
        /// <param name="Table">表名</param>
        /// <param name="Condition">条件</param>
        public static DataTable ReturnDataTableDistinc(string Address, string Column, string Table, string Condition)
        {
            DataTable dt = new DataTable();
            try
            {
                con = new SqlConnection(Address);
                string sqlCmd = "select DISTINCT " + Column + " from " + Table + " where " + Condition;
                SqlCommand cmd = new SqlCommand(sqlCmd, con);
                SqlDataAdapter sda = new SqlDataAdapter();
                sda.SelectCommand = cmd;
                DataSet ds = new DataSet();
                sda.Fill(ds, Table);
                dt = ds.Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return dt;
            }
        }

        /// <summary>
        /// 有条件查询MySQL并返回dataTable
        /// </summary>
        /// <param name="Address">数据库地址</param>
        /// <param name="Table">表名</param>
        /// <param name="Condition">条件</param>
        public static DataTable ReturnDataTable4(string Address, string Table, string Condition)
        {
            DataTable dt = new DataTable();
            try
            {
                con = new SqlConnection(Address);
                string sqlCmd = "select * from " + Table + " where " + Condition;
                SqlCommand cmd = new SqlCommand(sqlCmd, con);
                SqlDataAdapter sda = new SqlDataAdapter();
                sda.SelectCommand = cmd;
                DataSet ds = new DataSet();
                sda.Fill(ds, Table);
                dt = ds.Tables[0];
                return dt;
            }
            catch (Exception)
            {
                return dt;
            }
        }

        /// <summary>
        /// 特殊的，有条件正序查询MySQL并返回dataTable
        /// </summary>
        /// <param name="Address">数据库地址</param>
        /// <param name="Table">表名</param>
        /// <param name="Condition">条件</param>
        public static DataTable ReturnDataTable5(string Address, string Table, string Condition, string ColumnName)
        {
            DataTable dt = new DataTable();
            try
            {
                con = new SqlConnection(Address);
                string sqlCmd = "select * from " + Table + " where " + Condition + " order by CONVERT(" + ColumnName + " USING gbk ) COLLATE gbk_chinese_ci ASC";
                SqlCommand cmd = new SqlCommand(sqlCmd, con);
                SqlDataAdapter sda = new SqlDataAdapter();
                sda.SelectCommand = cmd;
                DataSet ds = new DataSet();
                sda.Fill(ds, Table);
                dt = ds.Tables[0];
                return dt;
            }
            catch (Exception)
            {
                return dt;
            }
        }

        /// <summary>
        /// 无条件倒序查询一定数量SQL并返回dataTable
        /// </summary>
        /// <param name="Address">数据库地址</param>
        /// <param name="Table">表名</param>
        public static DataTable ReturnDataTable6(string Address, string Table, int Quantity)
        {
            DataTable dt = new DataTable();
            try
            {
                con = new SqlConnection(Address);
                string sqlCmd = "select TOP " + Quantity + " * from " + Table + " order by ID desc";
                SqlCommand cmd = new SqlCommand(sqlCmd, con);
                SqlDataAdapter sda = new SqlDataAdapter();
                sda.SelectCommand = cmd;
                DataSet ds = new DataSet();
                sda.Fill(ds, Table);
                dt = ds.Tables[0];
                return dt;
            }
            catch (Exception)
            {
                return dt;
            }
        }

        /// <summary>
        /// 无条件有序查询某列并返回dataTable
        /// </summary>
        /// <param name="Address">数据库地址</param>
        /// <param name="Table">表名</param>
        /// <param name="Columns">列名</param>
        /// <param name="order">顺序条件</param>
        public static DataTable ReturnDataTable7(string Address, string Table, string Columns, string order)
        {
            DataTable dt = new DataTable();
            try
            {
                con = new SqlConnection(Address);
                string sqlCmd = $"select {Columns} from {Table} order by {order}";
                SqlCommand cmd = new SqlCommand(sqlCmd, con);
                SqlDataAdapter sda = new SqlDataAdapter();
                sda.SelectCommand = cmd;
                DataSet ds = new DataSet();
                sda.Fill(ds, Table);
                dt = ds.Tables[0];
                return dt;
            }
            catch (Exception)
            {
                return dt;
            }
        }

        /// <summary>
        /// 有条件倒序查询一定数量MySQL并返回dataTable
        /// </summary>
        /// <param name="Address">数据库地址</param>
        /// <param name="Table">表名</param>
        public static DataTable ReturnDataTable7(string Address, string Table, string Condition, int Quantity)
        {
            DataTable dt = new DataTable();
            try
            {
                con = new SqlConnection(Address);
                string sqlCmd = "select TOP " + Quantity + " * from " + Table + " where " + Condition + " order by ID desc";
                SqlCommand cmd = new SqlCommand(sqlCmd, con);
                SqlDataAdapter sda = new SqlDataAdapter();
                sda.SelectCommand = cmd;
                DataSet ds = new DataSet();
                sda.Fill(ds, Table);
                dt = ds.Tables[0];
                return dt;
            }
            catch (Exception)
            {
                return dt;
            }
        }

        /// <summary>
        /// 无条件无序查询MySQL并返回dataTable
        /// </summary>
        /// <param name="Address">数据库地址</param>
        /// <param name="Table">表名</param>
        public static DataTable ReturnDataTable8(string Address, string Table)
        {
            DataTable dt = new DataTable();
            try
            {
                con = new SqlConnection(Address);
                string sqlCmd = "select * from " + Table;
                SqlCommand cmd = new SqlCommand(sqlCmd, con);
                SqlDataAdapter sda = new SqlDataAdapter();
                sda.SelectCommand = cmd;
                DataSet ds = new DataSet();
                sda.Fill(ds, Table);
                dt = ds.Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return dt;
            }
        }

        /// <summary>
        /// 有条件无序查询MySQL并返回dataTable
        /// </summary>
        /// <param name="Address">数据库地址</param>
        /// <param name="Table">表名</param>
        /// <param name="Condition">条件</param>
        public static DataTable ReturnDataTable9(string Address, string Table, string Condition)
        {
            DataTable dt = new DataTable();
            try
            {
                con = new SqlConnection(Address);
                string sqlCmd = "select * from " + Table + " where " + Condition;
                SqlCommand cmd = new SqlCommand(sqlCmd, con);
                SqlDataAdapter sda = new SqlDataAdapter();
                sda.SelectCommand = cmd;
                DataSet ds = new DataSet();
                sda.Fill(ds, Table);
                dt = ds.Tables[0];
                return dt;
            }
            catch (Exception)
            {
                return dt;
            }
        }

        public static bool FindColumnName(string Address, string Table, string ColumnName)
        {
            try
            {
                con = new SqlConnection(Address);
                string sqlCmd = "select * from " + "`" + Table + "`";
                SqlCommand cmd = new SqlCommand(sqlCmd, con);
                SqlDataAdapter sda = new SqlDataAdapter();
                sda = new SqlDataAdapter();
                sda.SelectCommand = cmd;
                DataSet ds = new DataSet();
                ds = new DataSet();
                sda.Fill(ds, Table);
                List<string> Cname = new List<string>();
                for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
                {
                    Cname.Add(ds.Tables[0].Columns[i].ColumnName);
                }
                if (Cname.Contains(ColumnName))
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception)
            {
                return false;
            }
        }

        /// <summary>
        /// 判断给出列名的每一行是否有给定的行名，返回bool
        /// </summary>
        /// <param name="Address">数据库地址</param>
        /// <param name="Table">表名</param>
        /// <param name="RowName">给定的行名</param>
        /// <param name="ColumnName">给出的列名</param>
        public static bool FindRowName(string Address, string Table, string RowName, string ColumnName)
        {
            try
            {
                con = new SqlConnection(Address);
                string sqlCmd = "select * from " + Table;
                SqlCommand cmd = new SqlCommand(sqlCmd, con);
                SqlDataAdapter sda = new SqlDataAdapter();
                sda = new SqlDataAdapter();
                sda.SelectCommand = cmd;
                DataSet ds = new DataSet();
                ds = new DataSet();
                sda.Fill(ds, Table);
                List<string> Cname = new List<string>();
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    Cname.Add(ds.Tables[0].Rows[i][ColumnName].ToString());
                }
                if (Cname.Contains(RowName))
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception)
            {
                return false;
            }
        }

        /// <summary>
        /// 判断给出列名的每一行是否有给定的行名，返回bool
        /// </summary>
        /// <param name="Address">数据库地址</param>
        /// <param name="Table">表名</param>
        /// <param name="Condition">条件</param>
        /// <param name="RowName">给定的行名</param>
        /// <param name="ColumnName">给出的列名</param>
        public static bool FindRowName2(string Address, string Table, string Condition, string RowName, string ColumnName)
        {
            try
            {
                con = new SqlConnection(Address);
                string sqlCmd = "select * from " + Table + " where " + Condition + "";
                SqlCommand cmd = new SqlCommand(sqlCmd, con);
                SqlDataAdapter sda = new SqlDataAdapter();
                sda = new SqlDataAdapter();
                sda.SelectCommand = cmd;
                DataSet ds = new DataSet();
                ds = new DataSet();
                sda.Fill(ds, Table);
                List<string> Cname = new List<string>();
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    Cname.Add(ds.Tables[0].Rows[i][ColumnName].ToString());
                }
                if (Cname.Contains(RowName))
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception)
            {
                return false;
            }
        }

        /// <summary>
        /// 返回给定列名下的所有行内容，格式为list
        /// </summary>
        /// <param name="Address">数据库地址</param>
        /// <param name="Table">表名</param>
        /// <param name="ColumnName">给定的列名</param>
        public static List<string> ReturnTRowsName(string Address, string Table, string ColumnName)
        {
            List<string> RowsName = new List<string>();
            try
            {
                con = new SqlConnection(Address);
                string sqlCmd = "select * from " + "`" + Table + "`";
                SqlCommand cmd = new SqlCommand(sqlCmd, con);
                SqlDataAdapter sda = new SqlDataAdapter();
                sda = new SqlDataAdapter();
                sda.SelectCommand = cmd;
                DataSet ds = new DataSet();
                ds = new DataSet();
                sda.Fill(ds, Table);
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    if (ds.Tables[0].Rows[i][ColumnName].ToString() != "")
                    {
                        RowsName.Add(ds.Tables[0].Rows[i][ColumnName].ToString());
                    }
                }
                return RowsName;
            }
            catch (Exception)
            {
                return RowsName;
            }
        }

        /// <summary>
        /// 有条件的返回给定列名下的所有行内容，格式为list
        /// </summary>
        /// <param name="Address">数据库地址</param>
        /// <param name="Table">表名</param>
        /// <param name="ColumnName">给定的列名</param>
        /// <param name="Condition">条件</param>
        public static List<string> ReturnTRowsName2(string Address, string Table, string ColumnName, string Condition)
        {
            List<string> RowsName = new List<string>();
            try
            {
                con = new SqlConnection(Address);
                string sqlCmd = "select * from " + Table + " where " + Condition;
                SqlCommand cmd = new SqlCommand(sqlCmd, con);
                SqlDataAdapter sda = new SqlDataAdapter();
                sda = new SqlDataAdapter();
                sda.SelectCommand = cmd;
                DataSet ds = new DataSet();
                ds = new DataSet();
                sda.Fill(ds, Table);
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    if (ds.Tables[0].Rows[i][ColumnName].ToString() != "")
                    {
                        RowsName.Add(ds.Tables[0].Rows[i][ColumnName].ToString());
                    }
                }
                return RowsName;
            }
            catch (Exception)
            {
                return RowsName;
            }
        }

        /// <summary>
        /// 截断一个新表
        /// </summary>
        /// <param name="Address">数据库地址</param>
        /// <param name="Table">表名</param>
        public static bool TruncateTable(string Address, string Table)
        {
            try
            {
                string connetStr = Address;
                con = new SqlConnection(connetStr);
                con.Open();
                string sqlCmd = "truncate table " + Table;
                SqlCommand cmd = new SqlCommand(sqlCmd, con);
                int res = cmd.ExecuteNonQuery();
                con.Close();
                return true;
            }
            catch (Exception)
            {
                con.Close();
                return false;
            }
        }
    }

    public class IniFiles
    {
        public string inipath;

        //声明API函数
        [DllImport("kernel32")]
        private static extern long WritePrivateProfileString(string section, string key, string val, string filePath);

        [DllImport("kernel32")]
        private static extern int GetPrivateProfileString(string section, string key, string def, StringBuilder retVal, int size, string filePath);

        /// <summary>
        /// 构造方法
        /// </summary>
        /// <param name="INIPath">文件路径</param>
        public IniFiles(string INIPath)
        {
            inipath = INIPath;
        }

        public IniFiles()
        { }

        /// <summary>
        /// 写入INI文件
        /// </summary>
        /// <param name="Section">项目名称(如 [TypeName] )</param>
        /// <param name="Key">键</param>
        /// <param name="Value">值</param>
        public void IniWriteValue(string Section, string Key, string Value)
        {
            WritePrivateProfileString(Section, Key, Value, this.inipath);
        }

        /// <summary>
        /// 读出INI文件
        /// </summary>
        /// <param name="Section">项目名称(如 [TypeName] )</param>
        /// <param name="Key">键</param>
        public string IniReadValue(string Section, string Key)
        {
            StringBuilder temp = new StringBuilder(500);
            int i = GetPrivateProfileString(Section, Key, "", temp, 500, this.inipath);
            return temp.ToString();
        }

        /// <summary>
        /// 验证文件是否存在
        /// </summary>
        /// <returns>布尔值</returns>
        public bool ExistINIFile()
        {
            return File.Exists(inipath);
        }
    }
}